package com.two.commodityorder.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.two.commodityorder.dao.CommodityDao;
import com.two.commodityorder.model.Commodity;
import com.two.user.model.User;
import com.two.commodityorder.controller.DataBaseUtil;

public class CommodityDaoImpl implements CommodityDao{
	
	/** 查询所有的商品信息 */
	@Override
	public List<Commodity> selectAllCommodity() {
		List<Commodity> list = new ArrayList<>();
		Connection conn = DataBaseUtil.getConnection();
		String sql = "select * from spgl where flag = 1 limit 0,10";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Commodity spgl = new Commodity();
				spgl.setId(rs.getInt("id"));
				spgl.setCommodityName(rs.getString("commodityName"));
				spgl.setPrice(rs.getString("price"));
				list.add(spgl);
				spgl = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/** 根据商品名查询商品信息 */
	public Commodity selectByCommodityName(String commodityName) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "select * from spgl where commodityName = ?";
		PreparedStatement ps = null;
		ResultSet rs= null;
		Commodity spgl = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, commodityName);
			rs = ps.executeQuery();
			if(rs.next()) {
				spgl = new Commodity();
				spgl.setId(rs.getInt("id"));
				spgl.setCommodityName(rs.getString("commodityName"));
				spgl.setPrice(rs.getString("price"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//关闭资源
			DataBaseUtil.closeResource(conn, ps, rs);
		}
		return spgl;
	}
	
	 /** 添加商品 */
	@Override
	public int AddCommodity(String commodityName, String price) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "INSERT INTO spgl ( `commodityName`, `price` , `flag`) VALUES (?, ?, 1)";
		System.out.println(commodityName);
		System.out.println(price);
		PreparedStatement ps = null;
		ResultSet rs =null;
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, commodityName);
			ps.setString(2, price);
			int n = ps.executeUpdate();
			return n;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DataBaseUtil.closeResource(conn, ps, null);
		}
		return 0;
	}

	

	
	/** 更新商品 */
	@Override
	public int UpdateCommodity(int id,String commodityName, String price) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "UPDATE `spgl` SET  `commodityName`=?, `price`=? WHERE (`id`=?)";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, commodityName);
			ps.setString(2, price);
			ps.setInt(3, id);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DataBaseUtil.closeResource(conn, ps, null);
		}
		return n;
	
	}


	 /** 删除商品 */
	@Override
	public int DeleteCommodity(int id) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "update spgl set flag=0 where id=?";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DataBaseUtil.closeResource(conn, ps, null);
		}
		return n;
	}


	/** 根据编号查询商品信息 */
	@Override
	public Commodity selectByCommodityId(int id) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "select * from spgl where id = ?";
		PreparedStatement ps = null;
		ResultSet rs= null;
		System.out.println("Dao - commodityName: " + id);
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()) {
				Commodity spgl = new Commodity();
				spgl.setId(rs.getInt("id"));
				spgl.setCommodityName(rs.getString("commodityName"));
				spgl.setPrice(rs.getString("price"));
				return spgl;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	
	}

	

	
}